/*
* HomePageDaoImpl.java
* Created on  2014-5-14 下午8:47
* 版本       修改时间          作者      修改内容
* V1.0.1    2014-5-14       gaoxinyu    初始版本
*
*/
package com.genghis.ptas.homepage.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.constant.DictConstant;
import com.genghis.ptas.course.entity.CourseInfo;
import com.genghis.ptas.homepage.dao.HomePageDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 类的描述信息
 *
 * @author fengxuanzhen
 * @version 1.0.1
 */
@Repository("homePageDao")
public class HomePageDaoImpl extends PageDao implements HomePageDao {

    private StringBuilder getCourseSql() {
        String userSql = "SELECT * FROM course where 1=1 ";
        return new StringBuilder(userSql);
    }

    private StringBuilder getUserRegisterSql(int userId) {
        return new StringBuilder("SELECT * FROM course_register WHERE student_id=" + userId);
    }

    @Override
    public Page<CourseInfo> findCarouselPageList(PageParameter pageParameter, CourseInfo courseInfo) {
        StringBuilder sql = getCourseSql();
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(courseInfo), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public void editCarouselById(CourseInfo courseInfo) {
        String sql = "UPDATE course SET course_name = :courseName, chapter_num = :chapterNum, up_date = :upDate, state = :state , " +
                " course_type = :courseType, photo_url = :photoUrl WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseInfo));
    }

    @Override
    public List<CourseInfo> findCourseHotList(int userId) {
        String hotCourse = "SELECT c.*,cs.`average_score`,cs.`register_number` FROM course c \n" +
                "LEFT JOIN course_statistics cs ON cs.`course_id` = c.`id` WHERE c.state = 1 ORDER BY cs.register_number DESC LIMIT " + DictConstant.VOID_VALUE + "," + DictConstant.HOT_COURSE_VALUE;
        StringBuilder sql = new StringBuilder("SELECT al.*,us.student_id showRegister FROM ( ");
        sql.append(hotCourse).append(" ) al LEFT JOIN ( ").append(getUserRegisterSql(userId)).append(" ) us ON al.id=us.course_id ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public List<CourseInfo> findCourseSystemList(int userId) {
        String systemCourse = "SELECT course.*,cs.`quality`,cs.average_score FROM course LEFT JOIN `course_statistics` cs " +
                " ON course.`id` = cs.`course_id` WHERE course.state = 1 ORDER BY cs.quality DESC LIMIT " + DictConstant.VOID_VALUE + "," + DictConstant.SYSTEM_RECOMMEND;
        StringBuilder sql = new StringBuilder("SELECT al.*,us.student_id showRegister FROM ( ");
        sql.append(systemCourse).append(" ) al LEFT JOIN ( ").append(getUserRegisterSql(userId)).append(" ) us ON al.id=us.course_id ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public List<CourseInfo> findCourseInterestedListByCourseType(int userId) {
        String register = "SELECT c.course_type,COUNT(*) countR FROM `course_register` cr,course c  WHERE cr.`course_id`=c.id \n" +
                "AND cr.`student_id`=" + userId + " GROUP BY c.`course_type` ORDER BY countR DESC LIMIT 0,1";
        String interestedCourse = "SELECT c.*,cs.average_score,cs.total_time,cs.collect_number,cs.register_number,cs.total_number,cs.quality" +
                " FROM `course_statistics` cs, `course` c, (\n" + register + ") li \n" +
                "WHERE cs.`course_id`=c.`id` AND c.`course_type` AND li.course_type=c.`course_type` AND c.`id` NOT IN (\n" +
                "SELECT course_id FROM course_register WHERE student_id=" + userId + " )\n" +
                "ORDER BY `register_number` DESC LIMIT 0,2";

        StringBuilder sql = new StringBuilder("SELECT al.*,us.student_id showRegister FROM ( ");
        sql.append(interestedCourse).append(" ) al LEFT JOIN ( ").append(getUserRegisterSql(userId)).append(" ) us ON al.id=us.course_id ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public List<CourseInfo> findTopThreeCourseByType(String courseType) {
        String sql = "SELECT c.*,SUM(cr.`score`) AS score FROM course_register cr LEFT JOIN course c ON cr.`course_id` = c.`id`" +
                " LEFT JOIN type_values tv ON tv.`id` = c.`course_type` " +
                " WHERE c.state=1 AND  tv.`type_name` = ?  GROUP BY cr.course_id ORDER BY score DESC";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), courseType);
    }

    @Override
    public List<CourseInfo> findTopCourseByTypeValueId(int typeValueId) {
        String sql = "SELECT c.*,cs.average_score,cs.total_time,cs.collect_number,cs.register_number,cs.total_number,cs.quality" +
                " FROM course c LEFT JOIN course_statistics cs ON cs.`course_id` = c.`id` \n" +
                "WHERE c.state = 1 AND c.course_type =? ORDER BY cs.`average_score` DESC ";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), typeValueId);
    }

    @Override
    public List<CourseInfo> queryAllStudy() {
        return null;
    }

    @Override
    public List<CourseInfo> queryRank(String date) {
        String sql = "SELECT c.*,cs.`average_score`,cs.`register_number` FROM (\n" +
                "SELECT c.*,COUNT(*) registerNumber FROM `course_register` cr, course c  WHERE cr.`course_id`=c.id AND `register_date`>? GROUP BY `course_id`\n" +
                ") c LEFT JOIN course_statistics cs ON cs.`course_id` = c.`id` WHERE c.state = 1 ORDER BY cs.register_number DESC LIMIT " + DictConstant.VOID_VALUE + "," + DictConstant.QUERY_RANK;
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), date);
    }
}
